I'm making a simple application with Spring Boot (2.3.4) using MongoDB with Spring Data for MongoDB. I usually create queries for the app using the @Query annotation and it works very fine. But for an Aggregation I want to use, I built a query with the Criteria class. The criteria I need is like
where("primary").is(value).and("secondary").is("").
I need all entries where primary is equal to 'value' and secondary is empty. The query entered in MOngoDB Compass
{ $and: [ { primary: 'value' }, { secondary: ''} ] }
works as expected, but when I try to use the Criteria with Spring, it looks like the and part with the secondary is completely dropped. I get any results with 'value' in primary and with anything in secondary. This means an empty fields or anything else. Replacing the .is("") part with .regex("^$") didn't help.
This looks pretty basic to me, so what am I missing here? I don't want to replace the empty secondary with an "empty flag", because that feels wrong.
Update:
This is the code in question
Criteria crit;
if(!primary.equals(secondary)) {
crit = where("primary").is(primary.name()).and("secondary").is(secondary.name());
} else {
crit = where("primary").is(primary.name()).and("secondary").is("");
}
MatchOperation matchStage = Aggregation.match(crit);
GroupOperation groupStage = Aggregation.group("grouping").count().as("sum");
SortOperation sortStage = new SortOperation(Sort.by("_id"));
Aggregation aggregation = Aggregation.newAggregation(matchStage, groupStage, sortStage);
AggregationResults<TypePerGroup> results = mongoTemplate.aggregate(aggregation, "dataCollection", TypePerGroup.class);
This works with mongodb - Not sure what abstraction compass adds. Both queries don't generate the same json query but they are equal.
Generated query
where("primary").is(value).and("secondary").is("").
is
{"primary":value, "secondary": ""}
Perhaps compass doesn't like this variant ?
Anyways to generate query similar to what you have you input in compass you can use below code
Criteria criteria = new Criteria();
criteria.andOperator(Criteria.where("primary").is("hello"), Criteria.where("secondary").is(""));
Query query = Query.query(criteria);
You are not missing anything. where("primary").is(value).and("secondary").is("") is correct and is functionally equivalent to { $and: [ { primary: 'value' }, { secondary: ''} ] }. You should turn on debug level logging for MongoTemplate to see the generated query.
A have connected to Atlas using Mongo DBCompas and added 4 records to collection:
[{
"primary": "A",
"secondary": "A"
},{
"primary": "A",
"secondary": ""
},{
"primary": "B",
"secondary": "B"
},{
"primary": "B",
"secondary": ""
}]
both queries:
List<Data> firstResults = mongoTemplate.query(Data.class)
.matching(Query.query(Criteria.where("primary").is("B").and("secondary").is("")))
.all();
System.out.println(firstResults);
Criteria criteria = new Criteria();
criteria.andOperator(Criteria.where("primary").is("B"), Criteria.where("secondary").is(""));
List<Data> secondResults = mongoTemplate.query(Data.class)
.matching(Query.query(criteria))
.all();
System.out.println(secondResults);
gave the same result:
[Data{primary='B', secondary=''}]
Campfire can you please provide example of your code to analyze?